package com.gec.shop;

import java.sql.*;
import java.lang.Class;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

//数据链接 登录注册业务
public class Sql {
    static String url = "jdbc:mysql://localhost:3306/market?useUnicode=true&characterEncoding=utf-8";
    static String user = "root";
    static String password = "123456";

    //链接数据库
    public static Connection linkSQL() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    //关闭数据库链接
    public static void closeSQL(Connection connection){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //用户注册(会员或者员工)
    public static String addUser(Object obj) throws SQLException {
        Class<?> objClass = obj.getClass();
        Connection connection = linkSQL();
        PreparedStatement preparedStatement;
        ResultSet result;
        int i;
        //添加会员信息
        if(objClass.getSimpleName().equals("Member")){

            Member member = (Member)obj;
            String sql = "select member_phone from " + objClass.getSimpleName()+" where member_phone = "+member.getPhone()+";";
            preparedStatement = connection.prepareStatement(sql);
            result = preparedStatement.executeQuery();
            //手机未被注册信息则添加会员信息
            if(!result.next()){
                sql = "insert into "+objClass.getSimpleName()+" values(0,?,?,?,?,0);";
                preparedStatement = connection.prepareStatement(sql);

                preparedStatement.setString(1,member.getPassword());
                preparedStatement.setString(2,member.getName());
                preparedStatement.setString(3,member.getSex());
                preparedStatement.setLong(4,member.getPhone());

                i = preparedStatement.executeUpdate();
                //验证注册是否成功
                sql = "select member_id from " + objClass.getSimpleName()+" where member_phone = "+member.getPhone()+";";
                preparedStatement = connection.prepareStatement(sql);
                result = preparedStatement.executeQuery();

                int id = 0;
                while(result.next()){
                    id = result.getInt("member_id");
                }
                closeSQL(connection);

                if(i > 0){
                    return "你的账号id--->"+id+"\n注册成功！\n";
                }else{
                    return "注册失败";
                }
            }else{
                closeSQL(connection);
                return "注册失败";
            }
        }
        //添加员工信息
        else if(objClass.getSimpleName().equals("Staff")){
            Staff staff = (Staff)obj;
            String sql = "select staff_phone from " + objClass.getSimpleName()+" where staff_phone = "+staff.getPhone()+";";
            preparedStatement = connection.prepareStatement(sql);
            result = preparedStatement.executeQuery();
            //手机未被注册信息则添加会员信息
            if(!result.next()){
                sql = "insert into "+objClass.getSimpleName()+" values(0,?,?,?,?,?);";
                preparedStatement = connection.prepareStatement(sql);

                preparedStatement.setString(1,staff.getPassword());
                preparedStatement.setString(2,staff.getName());
                preparedStatement.setString(3,staff.getSex());
                preparedStatement.setLong(4,staff.getPhone());
                preparedStatement.setString(5,staff.getPost());

                i = preparedStatement.executeUpdate();
                //验证注册是否成功
                sql = "select staff_id from " + objClass.getSimpleName()+" where staff_phone = "+staff.getPhone()+";";
                preparedStatement = connection.prepareStatement(sql);
                result = preparedStatement.executeQuery();

                int id = 0;
                while(result.next()){
                    id = result.getInt("staff_id");
                }
                closeSQL(connection);

                if(i > 0){
                    return "你的账号id--->"+id+"\n注册成功！\n";
                }else{
                    return "注册失败";
                }
            }else{
                closeSQL(connection);
                return "注册失败";
            }
        }
        return null;
    }

    //用户登录
    public static String userLogin(Object obj) throws SQLException {
        try {
            Class<?> objClass = obj.getClass();
            Connection connection = linkSQL();
            PreparedStatement preparedStatement;
            ResultSet result;
            String str;
            //验证会员信息
            if (objClass.getSimpleName().equals("Member")) {
                Member member = (Member) obj;
                String sql = "select member_id,member_password,member_name from " + objClass.getSimpleName() + " where member_id = " + member.getId() + ";";
                preparedStatement = connection.prepareStatement(sql);
                result = preparedStatement.executeQuery();
                System.out.println(result);
                while (result.next()) {
                    if (result.getInt("member_id") == member.getId() && result.getString("member_password").equals(member.getPassword())) {
                        str = result.getString("member_name");
                        closeSQL(connection);
                        return "登录成功，欢迎" + str;
                    }
                }
                closeSQL(connection);
                return "id或者密码错误";
            }
            //验证员工信息
            if (objClass.getSimpleName().equals("Staff")) {
                Staff staff = (Staff)obj;
                String sql = "select staff_id,staff_password,staff_name,staff_post from " + objClass.getSimpleName() + " where staff_id = " + staff.getId() + ";";
                preparedStatement = connection.prepareStatement(sql);
                result = preparedStatement.executeQuery();
                System.out.println(result);
                while (result.next()) {
                    if (result.getInt("staff_id") == staff.getId() && result.getString("staff_password").equals(staff.getPassword())) {
                        str = result.getString("staff_name");
                        closeSQL(connection);
                        return "登录成功，欢迎" + str;
                    }
                }
                closeSQL(connection);
                return "id或者密码错误";
            }
            closeSQL(connection);
            return "";
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
            return "内部错误";
        }
    }
    //查询所有商品信息
    public static String queryCommodity() throws SQLException {
        Connection connection = linkSQL();
        String sql = "select * from commodity;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        ResultSet result = preparedStatement.executeQuery();
        StringBuilder allCommodity = new StringBuilder();
        if(!result.next()){
            closeSQL(connection);
            return "查询失败，数据库为空!!";
        }
        result.beforeFirst();
        while (result.next()){
            String str = "";
            if(result.getInt("commodity_quantity") == 0){
                str = "【售罄】";
            }
            allCommodity.append("id=").append(result.getInt("commodity_id"))
                    .append(",名称=").append(result.getString("commodity_name"))
                    .append(",单价=").append(result.getString("commodity_price"))
                    .append(",库存=").append(result.getString("commodity_quantity"))
                    .append(str).append("\n");
        }
        allCommodity.append("查询成功！");
        closeSQL(connection);
        return allCommodity.toString();
    }

    //添加到购物车的商品
    public static String addShoppingCart(int id,Commodity commodity) throws SQLException {
        try{
            if (commodity.getQuantity() == 0) {
                return "添加失败！添加商品的数量不能为0";
            }
            //查询是否有空订单
            Connection connection = linkSQL();
            String sqlSelect = "select * from member_order where order_isPayment=? and order_member_id=?;";
            PreparedStatement preparedStatement = connection.prepareStatement(sqlSelect);
            preparedStatement.setString(1, "false");
            preparedStatement.setInt(2, id);
            ResultSet result = preparedStatement.executeQuery();
            int i;
            int orderId = 0;
            if (!result.next()) {
                String sqlInsertOrd = "insert into member_order values(0,now(),0,?,'false');";//创建一个空订单，用于占位，获取id
                preparedStatement = connection.prepareStatement(sqlInsertOrd);
                preparedStatement.setInt(1, id);
                i = preparedStatement.executeUpdate();
                if (i <= 0) {
                    closeSQL(connection);
                    return "获取购物车失败!";
                }
            }
            i = 0;
            String sqlSelectOrdId = "select order_id from member_order where order_member_id=?;";
            preparedStatement = connection.prepareStatement(sqlSelectOrdId,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setInt(1, id);
            result = preparedStatement.executeQuery();
            result.beforeFirst();
            while (result.next()) {
                orderId = result.getInt("order_id");
            }
            //判断购物车中是否存在该商品
            String sqlSelectSame = "select * from bufferorder where bufferOrder_order_id=? and bufferOrder_commodity_id=?;";
            preparedStatement = connection.prepareStatement(sqlSelectSame,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setInt(1, orderId);
            preparedStatement.setInt(2, commodity.getId());
            result = preparedStatement.executeQuery();
            if (!result.next()) {
                //商品没有重复执行添加新数据
                String sqlInsertBuf = "insert into bufferorder values(?,?,?);";
                preparedStatement = connection.prepareStatement(sqlInsertBuf,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                preparedStatement.setInt(1, orderId);
                preparedStatement.setInt(2, commodity.getId());
                preparedStatement.setInt(3, commodity.getQuantity());
                i = preparedStatement.executeUpdate();
                if (i > 0) {
                    return "商品成功加入购物车";
                } else {
                    return "商品添加购物车失败";
                }
            }
            result.beforeFirst();
            int quantity = 0;
            while (result.next()) {
                quantity = result.getInt("bufferOrder_commodity_quantity");
            }
            //存在同样商品则修改它的数量
            String sqlUpadteBford = "update bufferorder set bufferOrder_commodity_quantity=? where bufferOrder_order_id=? and bufferOrder_commodity_id=?;";
            preparedStatement = connection.prepareStatement(sqlUpadteBford);
            preparedStatement.setInt(1,(commodity.getQuantity()+quantity));
            preparedStatement.setInt(2,orderId);
            preparedStatement.setInt(3,commodity.getId());
            i = preparedStatement.executeUpdate();
            closeSQL(connection);
            if (i > 0) {
                return "商品成功加入购物车";
            } else {
                return "商品添加购物车失败";
            }
        }catch(Exception e){
            e.printStackTrace();
            return "操作失败！！";
        }
    }
    //员工添加商品入库
    public static String addCommodity(Commodity commodity) throws SQLException {
        Connection connection = linkSQL();
        String sql = "insert into commodity values(0,?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,commodity.getName());
        preparedStatement.setDouble(2,commodity.getPrice());
        preparedStatement.setInt(3,commodity.getQuantity());
        int result = preparedStatement.executeUpdate();
        closeSQL(connection);
        if(result > 0){
            return "商品入库成功";
        }else{
            return "商品入库失败";
        }
    }
    //员工修改商品
    public  static  String modifyCommodity(Commodity commodity)throws SQLException{
        Connection connection = linkSQL();
        String sql = "update commodity set commodity_name=?,commodity_price=?,commodity_quantity=? where commodity_id=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,commodity.getName());
        preparedStatement.setDouble(2,commodity.getPrice());
        preparedStatement.setInt(3,commodity.getQuantity());
        preparedStatement.setInt(4,commodity.getId());
        int result = preparedStatement.executeUpdate();
        closeSQL(connection);
        if(result > 0){
            return "修改商品成功";
        }else{
            return "修改商品失败";
        }
    }
    //查询购物车中的商品
    public static String queryShoppingCart(int id)throws SQLException{
        //查询
        Connection connection = linkSQL();
        String sql = "select order_member_id,bufferOrder_order_id,bufferOrder_commodity_id,commodity_name,bufferOrder_commodity_quantity,commodity_price from (select order_member_id,bufferOrder_order_id,bufferOrder_commodity_id,bufferOrder_commodity_quantity from bufferorder b,(select order_id,order_member_id from member_order where order_member_id=? and order_isPayment='false')tab where tab.order_id =b.bufferOrder_order_id)tab,commodity c where c.commodity_id=tab.bufferOrder_commodity_id;";
        double totalPrice = 0;
        PreparedStatement preparedStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if(!resultSet.next()){
            closeSQL(connection);
            return "查询成功 购物车为空";
        }
        resultSet.beforeFirst();
        StringBuilder str = new StringBuilder();
        while(resultSet.next()){
            str.append("id=").append(resultSet.getInt("bufferOrder_commodity_id")).append("，名字=").append(resultSet.getString("commodity_name")).append("，数量=").append(resultSet.getInt("bufferOrder_commodity_quantity")).append("\n");
            totalPrice+=resultSet.getInt("bufferOrder_commodity_quantity")*resultSet.getDouble("commodity_price");
        }
        closeSQL(connection);
        return str+"查询成功 商品总价为："+totalPrice+"元";
    }
    //修改购物车中的商品
    public static String XgShoppingCart(int id,Commodity commodity)throws SQLException{
        try{
            if (commodity.getQuantity() == 0) {
                return "修改失败！修改商品的数量不能为0";
            }
            Connection connection = linkSQL();
            String sql = "select order_member_id,bufferOrder_order_id,bufferOrder_commodity_id,bufferOrder_commodity_quantity from bufferorder b,(select order_id,order_member_id from member_order where order_member_id=? and order_isPayment='false')tab where tab.order_id =b.bufferOrder_order_id;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setInt(1, id);
            ResultSet result = preparedStatement.executeQuery();
            if(!result.next()){
                closeSQL(connection);
                return "购物车为空";
            }
            result.beforeFirst();
            int i = 0;
            while(result.next()) {
                String sqlUpadteBford = "update bufferorder set bufferOrder_commodity_quantity=? where bufferOrder_order_id=? and bufferOrder_commodity_id=?;";
                preparedStatement = connection.prepareStatement(sqlUpadteBford);
//                preparedStatement.setInt(1,(result.getInt(commodity.getQuantity())));
                preparedStatement.setInt(1, commodity.getQuantity());
                preparedStatement.setInt(2,result.getInt("bufferOrder_order_id"));
                preparedStatement.setInt(3,commodity.getId());
                i = preparedStatement.executeUpdate();
                break;
            }
            closeSQL(connection);
            if (i > 0) {
                return "修改成功";
            } else {
                return "修改失败";
            }
        }catch(Exception e){
            e.printStackTrace();
            return "操作失败！！";
        }
    }
    //删除购物车中的商品
    public static String delectShoppingCart(int id,Commodity commodity)throws SQLException{
        try{
            if (commodity.getQuantity() == 0) {
                return "删除失败！删除商品的数量不能为0";
            }
            Connection connection = linkSQL();
            String sql = "select order_member_id,bufferOrder_order_id,bufferOrder_commodity_id,bufferOrder_commodity_quantity from bufferorder b,(select order_id,order_member_id from member_order where order_member_id=? and order_isPayment='false')tab where tab.order_id =b.bufferOrder_order_id;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setInt(1, id);
            ResultSet result = preparedStatement.executeQuery();
            if(!result.next()){
                closeSQL(connection);
                return "购物车为空";
            }
            result.beforeFirst();
            int i = 0;
            while(result.next()) {
                if(commodity.getQuantity() > result.getInt("bufferOrder_commodity_quantity")){
                    return "删除失败！删除商品的数量比购物车中的数量多";
                }
                else if(commodity.getQuantity() == result.getInt("bufferOrder_commodity_quantity")) {
                    String deleteBufCom = "delete from bufferorder where bufferOrder_order_id=? and bufferOrder_commodity_id=?";
                    preparedStatement = connection.prepareStatement(deleteBufCom);
                    preparedStatement.setInt(1, result.getInt("bufferOrder_order_id"));
                    preparedStatement.setInt(2, commodity.getId());
                    i = preparedStatement.executeUpdate();
                    break;
                }else{
                    String sqlUpadteBford = "update bufferorder set bufferOrder_commodity_quantity=? where bufferOrder_order_id=? and bufferOrder_commodity_id=?;";
                    preparedStatement = connection.prepareStatement(sqlUpadteBford);
                    preparedStatement.setInt(1,(result.getInt("bufferOrder_commodity_quantity")-commodity.getQuantity()));
                    preparedStatement.setInt(2,result.getInt("bufferOrder_order_id"));
                    preparedStatement.setInt(3,commodity.getId());
                    i = preparedStatement.executeUpdate();
                    break;
                }
            }
            closeSQL(connection);
            if (i > 0) {
                return "删除成功";
            } else {
                return "删除失败";
            }
        }catch(Exception e){
            e.printStackTrace();
            return "操作失败！！";
        }
    }
    //将购物车中的商品生成一个订单，并且支付
    public static String markOrder(int id) throws SQLException {
        try {
            Connection connection = linkSQL();
            String sql = "select order_member_id,bufferOrder_order_id,bufferOrder_commodity_id,commodity_name,bufferOrder_commodity_quantity,commodity_price from (select order_member_id,bufferOrder_order_id,bufferOrder_commodity_id,bufferOrder_commodity_quantity from bufferorder b,(select order_id,order_member_id from member_order where order_member_id=? and order_isPayment='false')tab where tab.order_id =b.bufferOrder_order_id)tab,commodity c where c.commodity_id=tab.bufferOrder_commodity_id;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet result = preparedStatement.executeQuery();
            if (!result.next()) {
                closeSQL(connection);
                return "购物车为空，生成订单失败";
            }
            result.beforeFirst();
            double totalPrice = 0;
            int orderId = 0;
            double balance = 0;
            //判断余额是否足够
            while (result.next()) {
                totalPrice += result.getInt("bufferOrder_commodity_quantity") * result.getDouble("commodity_price");
            }
            result.beforeFirst();
            String memberBalance = "select * from member where member_id=?";
            preparedStatement = connection.prepareStatement(memberBalance);
            preparedStatement.setInt(1, id);
            ResultSet balanceE = preparedStatement.executeQuery();
            while (balanceE.next()) {
                if (balanceE.getDouble("member_balance") < totalPrice) {
                    closeSQL(connection);
                    return "余额不足，支付失败";
                }
            }
            while (result.next()) {
                orderId = result.getInt("bufferOrder_order_id");
                String allCommodity = "select * from commodity where commodity_id=?";
                preparedStatement = connection.prepareStatement(allCommodity);
                preparedStatement.setInt(1, result.getInt("bufferOrder_commodity_id"));
                ResultSet exist = preparedStatement.executeQuery();
                if (!exist.next()) {
                    closeSQL(connection);
                    return "生成订单失败，部分商品不存在";
                }
                exist.beforeFirst();
                while (exist.next()) {
                    if (exist.getInt("commodity_quantity") < result.getInt("bufferOrder_commodity_quantity")) {
                        String commodity_name = exist.getString("commodity_name");
                        closeSQL(connection);
                        return "订单创建失败，商品" + commodity_name + "数量不足";
                    }
                }
            }
            balanceE.beforeFirst();
            while (balanceE.next()) {//扣钱操作
                String updateBalance = "update member set member_balance=? where member_id=?";
                preparedStatement = connection.prepareStatement(updateBalance);
                balance = balanceE.getDouble("member_balance") - totalPrice;
                preparedStatement.setDouble(1, balance);
                preparedStatement.setInt(2, id);
                preparedStatement.executeUpdate();
            }
            result.beforeFirst();
            while (result.next()) {//更新仓库商品数量
                orderId = result.getInt("bufferOrder_order_id");
                String allCommodity = "select * from commodity where commodity_id=?";
                preparedStatement = connection.prepareStatement(allCommodity);
                preparedStatement.setInt(1, result.getInt("bufferOrder_commodity_id"));
                ResultSet exist = preparedStatement.executeQuery();
                while (exist.next()) {
                    String updateQuantity = "update commodity set commodity_quantity=? where commodity_id=?";
                    preparedStatement = connection.prepareStatement(updateQuantity);
                    preparedStatement.setInt(1, (exist.getInt("commodity_quantity") - result.getInt("bufferOrder_commodity_quantity")));
                    preparedStatement.setInt(2, result.getInt("bufferOrder_commodity_id"));
                    int isUpdate = preparedStatement.executeUpdate();
                    if (isUpdate <= 0) {
                        closeSQL(connection);
                        return "系统异常";
                    }
                }
            }
            //更新订单表状态
            String setOrder = "update member_order set order_datetime=now(),order_totalPrice=?,order_isPayment=? where order_id=? and order_member_id=?";
            preparedStatement = connection.prepareStatement(setOrder);
            preparedStatement.setDouble(1, totalPrice);
            preparedStatement.setString(2, "true");
            preparedStatement.setInt(3, orderId);
            preparedStatement.setInt(4, id);
            int orderResult = preparedStatement.executeUpdate();
            if(orderResult<=0){
                //余额回滚
                balanceE.beforeFirst();
                while(balanceE.next()){
                    String updateBalance = "update member set member_balance=? where member_id=?";
                    preparedStatement = connection.prepareStatement(updateBalance);
                    balance = balanceE.getDouble("member_balance") - totalPrice;
                    preparedStatement.setDouble(1, balance);
                    preparedStatement.setInt(2, id);
                    preparedStatement.executeUpdate();
                }
                //仓库数量 回滚
                result.beforeFirst();
                while (result.next()) {//更新仓库商品数量
                    orderId = result.getInt("bufferOrder_order_id");
                    String allCommodity = "select * from commodity where commodity_id=?";
                    preparedStatement = connection.prepareStatement(allCommodity);
                    preparedStatement.setInt(1, result.getInt("bufferOrder_commodity_id"));
                    ResultSet exist = preparedStatement.executeQuery();
                    while (exist.next()) {
                        String updateQuantity = "update commodity set commodity_quantity=? where commodity_id=?";
                        preparedStatement = connection.prepareStatement(updateQuantity);
                        preparedStatement.setInt(1, (exist.getInt("commodity_quantity") - result.getInt("bufferOrder_commodity_quantity")));
                        preparedStatement.setInt(2, result.getInt("bufferOrder_commodity_id"));
                        int isUpdate = preparedStatement.executeUpdate();
                    }
                }
                closeSQL(connection);
                return "未知错误";
            }
            return "订单生成成功，余额为" + balance + "元";
        }catch (Exception e){
            e.printStackTrace();
            return "系统错误";
        }
    }
    //用户充值
    public static String addBalance(int id,Object obj) throws Exception{
        Class<?> objClass = obj.getClass();
        Connection connection = linkSQL();
        if(objClass.getSimpleName().equals("Member")){
            Member member = (Member)obj;
            String sql1 = "select member_balance from member where member_id=?";
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement1.setInt(1,id);
            ResultSet resultSet = preparedStatement1.executeQuery();
            double balance = 0;
            while (resultSet.next()){
                balance = resultSet.getDouble("member_balance");
            }
            String sql = "update member set member_balance=? where member_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1,balance + member.getBalance());
            preparedStatement.setInt(2,id);
            int i = preparedStatement.executeUpdate();
            if(i > 0){
                closeSQL(connection);
                return "充值成功";
            }else{
                closeSQL(connection);
                return "充值失败";
            }
        }
        closeSQL(connection);
        return "正在充值";
    }
    //员工打卡
    public static String clockIn(int id) throws SQLException {//打卡
        try {
            Connection connection = linkSQL();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new java.util.Date());
            int dayInWeek = calendar.get(Calendar.DAY_OF_WEEK) - 1;
            //读取规定好的上班时间
            String selectWorkTime = "select * from work_time where work_time_id=?;";
            PreparedStatement preparedStatement = connection.prepareStatement(selectWorkTime);
            preparedStatement.setInt(1, 1);//要使用的工作时间表
            ResultSet resultSet = preparedStatement.executeQuery();
            if (!resultSet.next()) {
                return "打卡失败！没有该工作表！";
            }
            resultSet.beforeFirst();
            boolean isHoliday = false;//是否为放假日
            boolean hasClockIn = false;//早上是否已打卡
            boolean late = true;//是否迟到
            boolean early = true;//是否早退
            boolean isToday = false;//最新的打卡表是否为今天
            String[] workShitf = null;//存储规定好的上班时间
            String[] closeTime = null;//存储规定好的下班时间
            double overtime = 0;//加班时间
            int sheetId = 0;//最新打卡表的id
            String morningTime = null;//员工早上打卡的时间
            //读取规定的上班时间
            if (!resultSet.next()) {
                return "获取工作时间表失败！没有规定好的工作时间表！";
            }
            resultSet.beforeFirst();
            while (resultSet.next()) {
                String[] holidays = resultSet.getString("work_time_holiday").split(",");
                for (String day : holidays) {
                    int i = Integer.parseInt(day);
                    if (i == 7) {
                        i = 0;
                    }
                    if (dayInWeek == i) {
                        isHoliday = true;
                    }
                }
                workShitf = resultSet.getString("work_time_work_shift").split(":");
                closeTime = resultSet.getString("work_time_close_time").split(":");

            }
            //判断最新的打卡记录是否为今天
            String time;
            String selectTime = "select * from timesheet t,(select max(timesheet_id) m from timesheet where timesheet_staff_id=?)tab where t.timesheet_id=tab.m;";
            preparedStatement = connection.prepareStatement(selectTime);
            preparedStatement.setInt(1, id);
            ResultSet resultToday = preparedStatement.executeQuery();
            if (resultToday.next()) {
                resultToday.beforeFirst();
                while (resultToday.next()) {
                    time = resultToday.getString("timeSheet_gotowork_time");
                    sheetId = resultToday.getInt("m");
                    if (time == null) {
                        break;
                    }
                    if (calendar.get(Calendar.YEAR) == Integer.parseInt(time.substring(0, 4))) {
                        if ((calendar.get(Calendar.MONTH) + 1) == Integer.parseInt(time.substring(5, 7))) {
                            if (calendar.get(Calendar.DAY_OF_MONTH) == Integer.parseInt(time.substring(8, 10))) {
                                isToday = true;
                            }
                        }
                    }
                }
            }
            //否者新建一个表，然后获取新的id
            if (!isToday) {
                String newSheet = "insert into timesheet values(0,?,null,null,null,null,null,null);";
                preparedStatement = connection.prepareStatement(newSheet);
                preparedStatement.setInt(1, id);
                preparedStatement.executeUpdate();
                String findTimesheetId = "select max(timesheet_id) m from timesheet where timesheet_staff_id=?;";
                preparedStatement = connection.prepareStatement(findTimesheetId);
                preparedStatement.setInt(1, id);
                ResultSet findId = preparedStatement.executeQuery();
                while (findId.next()) {
                    sheetId = findId.getInt("m");//??
                }
            }
            //是否打了上班卡
            String SelectWorkShift = "select timeSheet_gotowork_time from timesheet where timeSheet_staff_id=? and timesheet_id=?;";
            preparedStatement = connection.prepareStatement(SelectWorkShift);
            preparedStatement.setInt(1, id);
            preparedStatement.setInt(2, sheetId);
            ResultSet result = preparedStatement.executeQuery();
            while (result.next()) {
                if (result.getString("timeSheet_gotowork_time") != null) {
                    morningTime = result.getString("timeSheet_gotowork_time");
                    hasClockIn = true;
                }
            }


            //加班时间
            if (isHoliday && hasClockIn) {
                overtime += calendar.get(Calendar.HOUR_OF_DAY) - Double.parseDouble(morningTime.substring(11, 13));
                if (overtime == 0) {
                    if ((calendar.get(Calendar.MINUTE) - Double.parseDouble(morningTime.substring(14, 16))) >= 30) {
                        overtime += 0.5;
                    }
                } else {
                    if (Double.parseDouble(morningTime.substring(14, 16)) <= 30 && calendar.get(Calendar.MINUTE) >= 30 && (calendar.get(Calendar.MINUTE) - Double.parseDouble(morningTime.substring(14, 16))) >= 30) {
                        overtime += 0.5;
                    }
                }
            } else if (hasClockIn) {
                String workOff = "select timeSheet_overtime from timesheet where timeSheet_id=? and timeSheet_staff_id=?;";
                preparedStatement = connection.prepareStatement(workOff);
                preparedStatement.setInt(1, sheetId);
                preparedStatement.setInt(2, id);
                ResultSet workOffTime = preparedStatement.executeQuery();
                while (workOffTime.next()) {
                    if (workOffTime.getDouble("timeSheet_overtime") != 0) {
                        overtime = workOffTime.getDouble("timeSheet_overtime");
                    }
                }
            }


            //判断是否迟到早退
            if (!isHoliday) {
                if (!hasClockIn) {
                    if (calendar.get(Calendar.HOUR_OF_DAY) < Double.parseDouble(workShitf[0])) {
                        overtime += Double.parseDouble(workShitf[0]) - calendar.get(Calendar.HOUR_OF_DAY);
                        if (calendar.get(Calendar.HOUR_OF_DAY) <= 30 && calendar.get(Calendar.HOUR_OF_DAY) != 0) {
                            overtime -= 0.5;
                        }
                        late = false;
                    }
                    if (calendar.get(Calendar.HOUR_OF_DAY) == Double.parseDouble(workShitf[0])) {
                        if (calendar.get(Calendar.MINUTE) == Double.parseDouble(workShitf[1])) {
                            if (calendar.get(Calendar.SECOND) == Double.parseDouble(workShitf[2])) {
                                late = false;
                            }
                        }
                    }
                } else {
                    if (calendar.get(Calendar.HOUR_OF_DAY) > Double.parseDouble(closeTime[0])) {
                        early = false;
                    }

                    if (calendar.get(Calendar.HOUR_OF_DAY) == Double.parseDouble(closeTime[0])) {
                        if (calendar.get(Calendar.MINUTE) > Double.parseDouble(closeTime[1])) {
                            early = false;
                        }
                        if (calendar.get(Calendar.MINUTE) == Double.parseDouble(closeTime[1])) {
                            if (calendar.get(Calendar.SECOND) >= Double.parseDouble(closeTime[2])) {
                                early = false;
                            }
                        }
                    }
                }
            } else {
                early = false;
                late = false;
            }
            //打卡
            int i;
            String clockIn;
            if (!hasClockIn) {
                clockIn = "update timesheet set timeSheet_gotowork_time=now(),timeSheet_late=?,timeSheet_overtime=?,timeSheet_isHoliday=? where timesheet_staff_id=? and timesheet_id=?;";
                preparedStatement = connection.prepareStatement(clockIn);
                if (late) {
                    preparedStatement.setString(1, "true");
                } else {
                    preparedStatement.setString(1, "false");
                }
                preparedStatement.setDouble(2, overtime);
                if (isHoliday) {
                    preparedStatement.setString(3, "true");
                } else {
                    preparedStatement.setString(3, "false");
                }
                preparedStatement.setInt(4, id);
                preparedStatement.setInt(5, sheetId);
                i = preparedStatement.executeUpdate();
                closeSQL(connection);
                if (i > 0) {
                    if (late) {
                        return "上班打卡成功！你迟到了！";
                    }
                    return "上班打卡成功！";
                }
                closeSQL(connection);
                return "上班打卡失败！";
            } else {
                clockIn = "update timesheet set timeSheet_gooffwork_time=now(),timeSheet_leave_early=?,timeSheet_overtime=? where timesheet_staff_id=? and timesheet_id=?;";
                preparedStatement = connection.prepareStatement(clockIn);
                if (early) {
                    preparedStatement.setString(1, "true");
                } else {
                    preparedStatement.setString(1, "false");
                }
                preparedStatement.setDouble(2, overtime);
                preparedStatement.setInt(3, id);
                preparedStatement.setInt(4, sheetId);
                i = preparedStatement.executeUpdate();
                closeSQL(connection);
                if (i > 0) {
                    if (early) {
                        return "下班打卡成功！你早退了！";
                    }
                    return "下班打卡成功！";
                }
                closeSQL(connection);
                return "下班打卡失败！";
            }
        } catch (Exception e) {
            return "操作失败！！就你小子捣乱是吧？嗯？爷这就把你的号封了！";
        }
    }

    /**
     * 员工查取考勤表
     *
     * @param id 用户id
     * @return 信息提示
     */
    public static String attendanceRecord(int id) throws SQLException, ParseException {
        try {
            Connection connection = linkSQL();
            //查询员工所有打卡记录
            String sql = "select * from timesheet where timeSheet_staff_id=? and timeSheet_isHoliday=?;";
            StringBuilder str = new StringBuilder();
            int late = 0;
            int early = 0;
            String[] holiday;//工作时间表上的放假日期【单位：星期】
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new java.util.Date());
            int year = calendar.get(Calendar.YEAR);
            int month = calendar.get(Calendar.DAY_OF_MONTH);
            int maxDays = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
            int j = 0;//工作日天数
            int workdaysOfMonth = 0;//当月工作日打卡天数
            String time;//当月所有日期【在循环中使用】
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, "false");
            ResultSet resultSet = preparedStatement.executeQuery();
            if (!resultSet.next()) {
                closeSQL(connection);
                return "查询失败！没有打卡记录！";
            }
            resultSet.beforeFirst();
            //获取当月工作日打卡天数
            calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            String mon;
            if ((calendar.get(Calendar.MONTH) + 1) <= 9) {
                mon = "0" + (calendar.get(Calendar.MONTH) + 1);
            } else {
                mon = String.valueOf(calendar.get(Calendar.MONTH) + 1);
            }
            String yearMon = calendar.get(Calendar.YEAR) + "-" + mon;
            boolean yesornot = false;
            if (resultSet.next()) {
                yesornot = true;
            }
            if (yesornot) {
                resultSet.beforeFirst();
                while (resultSet.next()) {
                    if (resultSet.getString("timeSheet_gotowork_time").contains(yearMon)) {
                        workdaysOfMonth += 1;
                    }
                }
            }
            resultSet.beforeFirst();

            //查询考勤
            str.append("\t\t\t\t\t\t\t\t\t上班\t\t\t\t\t\t\t\t下班\t\t\t\t\t\t加班\n");
            String workDays = "select * from work_time where work_time_id=?;";
            preparedStatement = connection.prepareStatement(workDays);
            preparedStatement.setInt(1, 1);
            ResultSet result = preparedStatement.executeQuery();
            while (result.next()) {
                holiday = result.getString("work_time_holiday").split(",");
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                for (int i = 0; i < maxDays; i++) {
                    time = year + "-" + month + "-" + (i + 1);
                    calendar.setTime(simpleDateFormat.parse(time));
                    String strTime;
                    if ((i + 1) < 10) {
                        strTime = yearMon + "-0" + (i + 1);
                    } else {
                        strTime = yearMon + "-" + (i + 1);
                    }
                    str.append(strTime + "-->");
                    int times = 0;
                    for (String date : holiday) {
                        int dates = Integer.parseInt(date);
                        if (dates == 7) {
                            dates = 0;
                        }
                        if ((calendar.get(Calendar.DAY_OF_WEEK) - 1) != dates) {
                            times += 1;
                        }
                        if (times == 2) {
                            j += 1;
                        }
                    }
                    String record = "select * from timesheet where timeSheet_staff_id=?;";
                    preparedStatement = connection.prepareStatement(record);
                    preparedStatement.setInt(1, id);
                    resultSet = preparedStatement.executeQuery();
                    boolean isN = false;
                    while (resultSet.next()) {
                        String workLate = "\t\t";
                        String workEarly = "\t\t";
                        if (resultSet.getString("timeSheet_late") != null && resultSet.getString("timeSheet_late").equals("true")) {
                            workLate = "【迟到】";
                        }
                        if (resultSet.getString("timeSheet_leave_early") != null && resultSet.getString("timeSheet_leave_early").equals("true")) {
                            workEarly = "【早退】";
                        }
                        String isHolidays;
                        if (resultSet.getString("timeSheet_isHoliday").equals("true")) {
                            isHolidays = "放假日";
                        } else {
                            isHolidays = "工作日";
                        }


                        if (resultSet.getString("timeSheet_gooffwork_time") == null && resultSet.getString("timeSheet_gotowork_time").contains(strTime)) {
                            str.append("\t【").append(isHolidays).append("】").append("\t" + resultSet.getString("timeSheet_gotowork_time") + workLate).append("\t\t\t\t\t\t\t\t\t").append(resultSet.getString("timeSheet_overtime") + "\n");
                            isN = true;
                        } else if (resultSet.getString("timeSheet_gotowork_time").contains(strTime)) {
                            str.append("\t【").append(isHolidays).append("】").append("\t").append(resultSet.getString("timeSheet_gotowork_time") + workLate).append("\t" + resultSet.getString("timeSheet_gooffwork_time") + workEarly + "\t").append(resultSet.getString("timeSheet_overtime") + "\n");
                            isN = true;
                        }
                    }
                    if (!isN) {
                        str.append("\n");
                    }
                }
            }
            resultSet.beforeFirst();
            result.beforeFirst();
            //重新查表计算迟到早退
            while (resultSet.next()) {
                if (resultSet.getString("timeSheet_late") != null && resultSet.getString("timeSheet_late").equals("true")) {
                    late += 1;
                }
                if (resultSet.getString("timeSheet_leave_early") != null && resultSet.getString("timeSheet_leave_early").equals("true")) {
                    early += 1;
                }
                if (resultSet.getString("timeSheet_gotowork_time") == null || resultSet.getString("timeSheet_gooffwork_time") == null) {
                    workdaysOfMonth -= 1;
                }
            }
            closeSQL(connection);
            return str + "\n工作日缺卡天数：" + (j - workdaysOfMonth) + "天\n总共迟到：" + late + "次,总共早退：" + early + "次\n查询成功！";
        } catch (Exception e) {
            return "操作失败！！就你小子捣乱是吧？嗯？爷这就把你的号封了！";
        }
    }
}
